<? 
ob_start();
session_start();
		if(empty($HTTP_SESSION_VARS['user_login'])){
			echo"
			<tr>
			 <td align=center colspan=3>
			 <br><br><br>".$_SESSION['user_login']."
				Maaf, Anda tidak bisa mengakses halaman ini, Anda harus login terlebih dahulu ... <br><br>
				[ <a href='$path/index.php'>silahkan login</a> ]
			 </td>
			</tr>
			";
			exit();
		}
		include("../lib/config.php");
		include("../lib/function.php");
		include("../lib/header.php");
		$divisi 	= $HTTP_GET_VARS['divisi'];
		$st			= $HTTP_GET_VARS['st'];
		$stat_kar	= $HTTP_GET_VARS['stat_kar'];
		$agama		= $HTTP_GET_VARS['agama'];
		$gender		= $HTTP_GET_VARS['gender'];
		$marital	= $HTTP_GET_VARS['marital'];
		$excel		= $HTTP_GET_VARS['excel'];

// cek hak akses untuk cabang
		$sad = mssql_query("SELECT nama_menu FROM VPrivUser A, VCabang B WHERE username='".$HTTP_SESSION_VARS['user_login']."' AND baca='1' AND A.nama_menu=B.Kode_Cabang",$conn);
		while($rad = mssql_fetch_array($sad)){
		// VARIABLE UNTUK CABANG
		$VAreaCode[] = $rad[0];
		}
/////////////////////////////////
	?>
<title>Laporan Pegawai Pribadi</title>
<?
	if($excel == "yes"){
	}
	else{
?>
<script type="text/javascript">
function Show_Excel(){
	var divisi 		= document.forms[0].elements['divisi'].value;
	var stat_ker 	= document.forms[0].elements['stat_kar'].value;
	var stat_kar 	= document.forms[0].elements['st'].value;
	var gender 		= document.forms[0].elements['gender'].value;
	var marital 	= document.forms[0].elements['marital'].value;
	var agama	 	= document.forms[0].elements['agama'].value;
	
	location.href = "<?=$PHP_SELF?>?divisi="+divisi+"&st="+stat_kar+"&excel=yes&stat_kar="+stat_ker+"&agama="+agama+"&gender="+gender+"&marital="+marital+"";
}	
</script>
<link rel="stylesheet" type="text/css" href="../style/style.css" />
 <div style="font-size:14px; border-bottom:solid 1px #000000; font-weight:bold;">Laporan Pegawai Pribadi</div><br>
<fieldset>
 <legend>Filter Data</legend>
 <form action="<?=$PHP_SELF?>" method="get">
 <table border="0">
  <tr>
   <td>Divisi/Biro/Cabang</td>
   <td><select style='width:350px;' name='divisi'>
	    <option value='all'>--semua--</option>
        <?		
		$sqljd  = "SELECT * FROM VLevelCabang WHERE status='1' ";
		$sqljd .= "AND AreaCode IN (";

		for($k=0;$k<count($VAreaCode);$k++){
			$sqljd .= "'$VAreaCode[$k]',";
		}

		$sqljd .= "'$VAreaCode[0]') ";		
		
		$sqljd .= " ORDER BY uraian ASC";
		
		$sq2 = mssql_query($sqljd,$conn);
		while($rq2 = mssql_fetch_array($sq2)){
			echo"<option value='".$rq2[kode_so]."'";
			if($divisi == $rq2[kode_so]){
				echo" selected";
			}
			echo">$rq2[uraian]</option>";
		}
		?>
        </select></td>
   <td align="right">Agama</td>
   <td><select name="agama">
	    <option value='all'>--semua--</option>
        <?
		$sag = mssql_query("SELECT * FROM VAgama ORDER BY nama ASC",$conn);
		while($rag = mssql_fetch_array($sag)){
			echo"<option value='".$rag[kode]."'";
			if($agama == $rag[kode]){
				echo" selected";
			}
			echo">$rag[nama]</option>";
		}
		?>
   </select></td>
  </tr>
  <tr>
   <td>Status Kerja</td>
   <td width="300"><select name='stat_kar' style='width:350px;'>
	    <option value='all'>--semua--</option>
        <?
	$sq = mssql_query("SELECT * FROM VStatusKaryawan",$conn);
	while($rq = mssql_fetch_array($sq)){
		echo"<option value='".$rq[kode]."'";
		if($stat_kar == $rq[kode]){
			echo" selected";
		}
		echo">$rq[Nama]</option>";
	}
		?>
        </select></td>
   <td align="right">Status Pernikahan</td>
   <td><select name="marital">
	    <option value='all'>--semua--</option>
        <?
		$skw = mssql_query("SELECT * FROM VStatusKawin ORDER BY nama ASC",$conn);
		while($rkw = mssql_fetch_array($skw)){
			echo"<option value='".$rkw[kode]."'";
			if($marital == $rkw[kode]){
				echo" selected";
			}
			echo">$rkw[nama]</option>";
		}
		?>
   </select></td>
   </tr>
   <tr>
   <td>Status Karyawan</td>
   <td><select name="st">
   <option value="all">--semua--</option>
   <option value="bekerja" <? if($st=="bekerja"){ echo" selected"; } ?>>Bekerja</option>
   <option value="berhenti" <? if($st=="berhenti"){ echo" selected"; } ?>>Berhenti</option>
   </select></td>
   <td align="right">Jenis Kelamin</td>
   <td><select name="gender">
	    <option value='all'>--semua--</option>
        <option value="0" <? if($gender=="0"){ echo" selected"; } ?>>Pria</option>
        <option value="1" <? if($gender=="1"){ echo" selected"; } ?>>Wanita</option>
      </select></td>
  </tr>
  <tr>
   <td>&nbsp;</td>
   <td><input type="submit" class="button" value="Cari"><input type="button" value="Kirim ke Excel" onclick="Javascript:Show_Excel();" class="button" /></td>
  </tr>
 </table>
 </form> 
</fieldset>
<?
// end if xecle
}

	$sqlcount = "SELECT A.* FROM VLatestKarir A, vlevelcabang B WHERE NIK is not null AND A.kode_so2=B.kode_so ";

	$sqlcount .= "AND B.AreaCode IN (";

	for($k=0;$k<count($VAreaCode);$k++){
		$sqlcount .= "'$VAreaCode[$k]',";
	}

	$sqlcount .= "'$VAreaCode[0]') ";		

	if(!empty($st)){
		if($st == "all"){
		
		}
		else{
			if($st == "berhenti"){
				$sqlcount .= " AND Jenis IN ('U','H') ";			
			}
			elseif($st == "bekerja"){
				$sqlcount .= " AND Jenis NOT IN ('U','H') ";
			}
		}
	}
	
	if(!empty($gender)){
		if($gender == "all"){
		
		}
		else{
			$sqlcount .= " AND JKel='$gender'";
		}
	}
	
	if(!empty($marital)){
		if($marital == "all"){
		
		}
		else{
			$sqlcount .= " AND Marital='$marital'";
		}
	}
	
	if(!empty($agama) && $agama != 0){
		if($agama == "all"){
		
		}
		else{
			$sqlcount .= " AND Agama='$agama'";
		}
	}

	if(!empty($divisi)){
		if($divisi == "all"){
		}
		else{
			$sqlcount .= " AND substring(kode_so2,1,7)='".substr($divisi,0,7)."'";
		}
	}
	
	if(!empty($stat_kar)){
		if($stat_kar == "all"){		
		}
		else{
			$sqlcount .= " AND stat_kar2='$stat_kar' ";
		}
	}
	
$result	= mssql_query($sqlcount,$conn);
$total	= mssql_num_rows($result);
$limit	= 20;
$pager  = Pager::getPagerData($total, $limit, $page);
$offset = $pager->offset;
$limit  = $pager->limit;
$page   = $pager->page;

if($total > 0)
{
	$sql = "WITH AAA AS(SELECT ROW_NUMBER() OVER(ORDER BY Nama ASC) AS No, A.*, CONVERT(CHAR(12), Tgl_Lahir, 103) TglLahir, YEAR(Tgl_lahir) thn_lahir FROM VLatestKarir A, vlevelcabang B WHERE NIK is not null AND A.kode_so2=B.kode_so ";
	
	$sql .= "AND B.AreaCode IN (";

	for($k=0;$k<count($VAreaCode);$k++){
		$sql .= "'$VAreaCode[$k]',";
	}

	$sql .= "'$VAreaCode[0]') ";		
	
	if(!empty($st)){
		if($st == "all"){
		
		}
		else{
			if($st == "berhenti"){
				$sql .= " AND Jenis IN ('U','H') ";			
			}
			elseif($st == "bekerja"){
				$sql .= " AND Jenis NOT IN ('U','H') ";
			}
		}
	}

	if(!empty($gender)){
		if($gender == "all"){
		
		}
		else{
			$sql .= " AND JKel='$gender'";
		}
	}
	
	if(!empty($marital)){
		if($marital == "all"){
		
		}
		else{
			$sql .= " AND Marital='$marital'";
		}
	}
	
	if(!empty($agama) && $agama != "0"){
		if($agama == "all"){
		
		}
		else{
			$sql .= " AND Agama='$agama'";
		}
	}
	
	if(!empty($divisi)){
		if($divisi == "all"){
		}
		else{
			$sql .= " AND substring(kode_so2,1,7)='".substr($divisi,0,7)."'";
		}
	}
	
	if(!empty($stat_kar)){
		if($stat_kar == "all"){		
		}
		else{
			$sql .= " AND stat_kar2='$stat_kar' ";
		}
	}
	
	$sql .= ") SELECT * FROM AAA";
	
	if($excel == "yes"){
	
	}
	else{
		$sql .= " WHERE No BETWEEN ".$offset." AND ".($offset+$limit)."";
	}
	
	$query = mssql_query($sql,$conn);
	
	if($excel == "yes"){
	
	}
	else{
		PagingLap($pager,$page,$next,$limit,$keyword,$show,$conn,$st,$divisi,$stat_kar,$agama,$gender,$marital);
	}
	
	if($excel == "yes"){
	
	}
	else{
?>
<fieldset>
 <legend>Hasil Pencarian</legend>
<?
	}
?>
 <table width="100%">
  <tr class="headlist">
   <td>No.</td>
   <td>NIK</td>
   <td>Nama</td>
   <td>Divisi/Biro/Cabang</td>
   <td>Jabatan</td>
   <td>Fungsional</td>
   <td>Tempat Lahir</td>
   <td>Tanggal Lahir</td>
   <td>Usia</td>
   <td>Status Kawin</td>
   <td>Jenis Kelamin</td>
   <td>Agama</td>
   <td>Alamat</td>
   <td>Status Karyawan</td>
   <td>Status Kerja</td>
   <td>Tgl. Masuk</td>
   <td>Lama Bekerja</td>
  </tr>
  <?
  while($row = mssql_fetch_array($query)){
  ?>
  <tr class="contentlist" style="font-size:smaller;">
   <td align="right"><?=$row[No]?></td>
   <td><?=$row[NIK]?></td>
   <td><?=$row[Nama]?></td>
   <td>
   <?
   // get divisi
	$kode_so = $row[kode_so2];
	if(empty($kode_so)){
		
	}
	else{
		$sdiv = mssql_query("SELECT uraian FROM VLevelCabang WHERE substring(kode_so,1,7)='".substr($kode_so,0,7)."'");
		$rdiv = mssql_fetch_array($sdiv);
		echo"$rdiv[0]";
	}
   ?>
   </td>
   <td>
   <?
   // get jabatan
   	if(empty($row[jabatan2])){
	
	}
	else{
		$jabatan = $row[jabatan2];
		$sjab	= mssql_query("SELECT Jabatan FROM MstJabatan WHERE kode='$jabatan'",$conn);
		$rjab	= mssql_fetch_array($sjab);
		echo"$rjab[0]";
	}
   ?>
   </td>
   <td>
   <?
   // get fungsional
   	if(empty($row[fungsi2])){
	
	}
	else{
		$fungsi = $row[fungsi2];
		$sfun	= mssql_query("SELECT Nama FROM MstFungsional WHERE kode='$fungsi'",$conn);
		$rfun	= mssql_fetch_array($sfun);
		echo"$rfun[0]";
	}
   ?>
   </td>
   <td><?=$row[TempatLahir]?></td>
   <td><?=$row[TglLahir]?></td>
   <td><?
   if(empty($row[Tgl_Lahir])){
   
   }
   else{
   		echo (date("Y") - $row[thn_lahir]);
   }
   ?></td>
   <td>
    <?
    // status kawin
	if(empty($row[Marital])){
		echo"&nbsp;";
	}
	else{
		$sstkwn = mssql_query("SELECT nama FROM VStatusKawin WHERE kode='$row[Marital]'");
		$rstkwn = mssql_fetch_array($sstkwn);
		echo $rstkwn[nama];
	}
	?>
   </td>
   <td>
    <?
    	// gender
		if($row[JKel] == 1){
			echo"Wanita";
		}
		else{
			echo"Pria";
		}
	?>
   </td>
   <td>
    <?
    	// agama
		$ssag = mssql_query("SELECT nama FROM VAgama WHERE kode='$row[Agama]'");
		$rsag = mssql_fetch_array($ssag);
		echo $rsag[nama];
	?>
   </td>
   <td><?=$row[Alamat]?></td>
   <td>
   <?
   // status karyawan
   	if(empty($row[stat_kar2])){
	
	}
	else{
		$stat_kar2 = $row[stat_kar2];
		$sstat_kar2	= mssql_query("SELECT Nama FROM VStatusKaryawan WHERE kode='$stat_kar2'",$conn);
		$rstat_kar2	= mssql_fetch_array($sstat_kar2);
		echo"$rstat_kar2[0]";
	}
   ?>
   </td>
   <td>
    <?
    // status kerja
	if($row[Jenis] == "U" || $row[Jenis] == "H"){
		echo"Berhenti";
	}
	else{
		echo"Bekerja";
	}
	?>
   </td>
   <td>
   <?
   // tgl masuk
   $stglmasuk = mssql_query("SELECT CONVERT(CHAR(12), tgl_efektif, 103) tgl, year(tgl_efektif) lama_kerja FROM VFirstKarir WHERE NIK='$row[NIK]'",$conn);
   $rtglmasuk = mssql_fetch_array($stglmasuk);
   echo"$rtglmasuk[tgl]";
   ?>
   </td>
   <td>
   <!--- lama bekerja -->
	<?
    	echo (date("Y") - $rtglmasuk[lama_kerja]);
	?> tahun
   </td>
  </tr>
  <?
  }
  ?>
 </table>
 <?
 	if($excel == "yes"){
		header('Content-type: application/vnd.ms-excel');
		header("Content-Disposition: attachment; filename=LaporanPegawaiPribadi".date("dmY").".xls");
		header("Pragma: no-cache");
		header("Expires: 0");
	}
	else{
 ?>
</fieldset>
<?
	}
}
else{
	//echo $total;
	echo"<br><br><div align=center>DATA TIDAK DITEMUKAN ...</div>";
}
?>